package Dao;

import entity.ScenicSpot;
import util.DBConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ScenicSpotDao {

    /**
     * 获取所有景点信息
     *
     * @return 景点列表
     * @throws SQLException 数据库异常
     */
    public List<ScenicSpot> getAllScenicSpots() throws SQLException {
        List<ScenicSpot> spots = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 获取数据库连接
            conn = DBConnection.getConnection();

            // 编写 SQL 查询语句
            String sql = "SELECT SpotID, SpotName, SpotAddress, TicketPrice, SpotIntroduction, RemainingTickets FROM ScenicSpot";
            pstmt = conn.prepareStatement(sql);

            // 执行查询
            rs = pstmt.executeQuery();

            // 遍历结果集，封装为 ScenicSpot 对象
            while (rs.next()) {
                ScenicSpot spot = new ScenicSpot();
                spot.setSpotID(rs.getInt("SpotID"));
                spot.setSpotName(rs.getString("SpotName"));
                spot.setSpotAddress(rs.getString("SpotAddress"));
                spot.setTicketPrice(rs.getBigDecimal("TicketPrice"));
                spot.setSpotIntroduction(rs.getString("SpotIntroduction"));
                spot.setRemainingTickets(rs.getInt("RemainingTickets"));
                spots.add(spot);
            }
        } finally {
            // 关闭资源
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }

        return spots;
    }

    /**
     * 根据景点 ID 获取景点信息
     *
     * @param spotID 景点 ID
     * @return 景点对象
     * @throws SQLException 数据库异常
     */
    public ScenicSpot getScenicSpotByID(int spotID) throws SQLException {
        ScenicSpot spot = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 获取数据库连接
            conn = DBConnection.getConnection();

            // 编写 SQL 查询语句
            String sql = "SELECT SpotID, SpotName, SpotAddress, TicketPrice, SpotIntroduction, RemainingTickets FROM ScenicSpot WHERE SpotID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, spotID);

            // 执行查询
            rs = pstmt.executeQuery();

            // 如果查询到结果，封装为 ScenicSpot 对象
            if (rs.next()) {
                spot = new ScenicSpot();
                spot.setSpotID(rs.getInt("SpotID"));
                spot.setSpotName(rs.getString("SpotName"));
                spot.setSpotAddress(rs.getString("SpotAddress"));
                spot.setTicketPrice(rs.getBigDecimal("TicketPrice"));
                spot.setSpotIntroduction(rs.getString("SpotIntroduction"));
                spot.setRemainingTickets(rs.getInt("RemainingTickets"));
            }
        } finally {
            // 关闭资源
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }

        return spot;
    }

    /**
     * 添加新景点
     *
     * @param spot 景点对象
     * @return 受影响的行数
     * @throws SQLException 数据库异常
     */
    public int addScenicSpot(ScenicSpot spot) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rowsAffected = 0;

        try {
            // 获取数据库连接
            conn = DBConnection.getConnection();

            // 编写 SQL 插入语句
            String sql = "INSERT INTO ScenicSpot (SpotName, SpotAddress, TicketPrice, SpotIntroduction, RemainingTickets) VALUES (?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, spot.getSpotName());
            pstmt.setString(2, spot.getSpotAddress());
            pstmt.setBigDecimal(3, spot.getTicketPrice());
            pstmt.setString(4, spot.getSpotIntroduction());
            pstmt.setInt(5, spot.getRemainingTickets());

            // 执行插入
            rowsAffected = pstmt.executeUpdate();
        } finally {
            // 关闭资源
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }

        return rowsAffected;
    }

    /**
     * 更新景点信息
     *
     * @param spot 景点对象
     * @return 受影响的行数
     * @throws SQLException 数据库异常
     */
    public int updateScenicSpot(ScenicSpot spot) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rowsAffected = 0;

        try {
            // 获取数据库连接
            conn = DBConnection.getConnection();

            // 编写 SQL 更新语句
            String sql = "UPDATE ScenicSpot SET SpotName = ?, SpotAddress = ?, TicketPrice = ?, SpotIntroduction = ?, RemainingTickets = ? WHERE SpotID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, spot.getSpotName());
            pstmt.setString(2, spot.getSpotAddress());
            pstmt.setBigDecimal(3, spot.getTicketPrice());
            pstmt.setString(4, spot.getSpotIntroduction());
            pstmt.setInt(5, spot.getRemainingTickets());
            pstmt.setInt(6, spot.getSpotID());

            // 执行更新
            rowsAffected = pstmt.executeUpdate();
        } finally {
            // 关闭资源
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }

        return rowsAffected;
    }

    /**
     * 删除景点
     *
     * @param spotID 景点 ID
     * @return 受影响的行数
     * @throws SQLException 数据库异常
     */
    public int deleteScenicSpot(int spotID) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rowsAffected = 0;

        try {
            // 获取数据库连接
            conn = DBConnection.getConnection();

            // 编写 SQL 删除语句
            String sql = "DELETE FROM ScenicSpot WHERE SpotID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, spotID);

            // 执行删除
            rowsAffected = pstmt.executeUpdate();
        } finally {
            // 关闭资源
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }

        return rowsAffected;
    }
}